{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "# matplotlib.use('Agg')\n",
    "import datetime\n",
    "from datetime import datetime as dt\n",
    "\n",
    "from pprint import pprint\n",
    "\n",
    "import sys\n",
    "sys.path.append(\"../FinRL\")\n",
    "\n",
    "import itertools\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocess fundamental data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df = pd.read_csv(\"sp500_fundamental_199601_202210.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_daily_price = pd.read_csv(\"sp500_price_19960101_20221021.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1843"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_daily_price.tic.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "935"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fund_df.tic.unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.1 Use Trade date instead of quarterly report date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# use trade date instead of report quarterly date\n",
    "times = list(fund_df['datadate']) # quarterly report date\n",
    "for i in range(len(times)):\n",
    "    quarter = (times[i] - int(times[i]/10000)*10000)\n",
    "    if 1201 < quarter:\n",
    "        times[i] = int(times[i]/10000 + 1)*10000 + 301\n",
    "    if quarter <= 301:\n",
    "        times[i] = int(times[i]/10000)*10000 + 301\n",
    "    if 301 < quarter <= 601:\n",
    "        times[i] = int(times[i]/10000)*10000 + 601\n",
    "    if 601 < quarter <= 901:\n",
    "        times[i] = int(times[i]/10000)*10000 + 901\n",
    "    if 901 < quarter <= 1201:\n",
    "        times[i] = int(times[i]/10000)*10000 + 1201\n",
    "    time_tmp = times[i]\n",
    "    year = int(time_tmp/10000)\n",
    "    month = int(quarter/100)\n",
    "    day = int(quarter - month*100)\n",
    "#     if(time_tmp < 20171114):\n",
    "#         while(dt.date(year, month, day).weekday() > 4):\n",
    "#             time_tmp = time_tmp + 1\n",
    "#             year = int(time_tmp/10000)\n",
    "#             month = int((time_tmp - year*10000)/100)\n",
    "#             day = int(time_tmp - year*10000 - month*100)\n",
    "#         times[i] = time_tmp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "times = pd.to_datetime(times,format='%Y%m%d')\n",
    "fund_df['tradedate']=times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.2 Calculate adjusted close price"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df['adj_close_q'] = fund_df.prccq/fund_df.adjex"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### match tickers and gvkey for fundamental and price data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "tic_to_gvkey = {}\n",
    "df_daily_groups = list(df_daily_price.groupby('tic'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "for tic, df_ in df_daily_groups:\n",
    "    tic_to_gvkey[tic] = df_.gvkey.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77711, 654)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "100"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(np.isin(fund_df.tic, list(tic_to_gvkey.keys()))==False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df = fund_df[np.isin(fund_df.tic, list(tic_to_gvkey.keys()))]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77611, 654)"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum(np.isin(fund_df.tic, list(tic_to_gvkey.keys()))==False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "930"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fund_df.gvkey.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df['gvkey'] = [tic_to_gvkey[x] for x in fund_df['tic']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "930"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(fund_df.gvkey.unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df['date'] = fund_df[\"tradedate\"]\n",
    "#fund_df.drop('tradedate', axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df['date']=pd.to_datetime(fund_df['date'], format=\"%Y%m%d\")\n",
    "fund_df.drop_duplicates([\"date\",\"gvkey\"], keep='last',inplace=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Get next quarter's return"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "l_df = list(fund_df.groupby('gvkey'))\n",
    "for tic,df in l_df:\n",
    "    df.reset_index(inplace=True, drop=True)\n",
    "    df.sort_values('date')\n",
    "    # our goal is to predict next quarter's return\n",
    "    df['y_return'] = np.log(df['adj_close_q'].shift(-1) / df['adj_close_q'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df = pd.concat([x[1] for x in l_df])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77585, 656)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>datadate</th>\n",
       "      <th>fyearq</th>\n",
       "      <th>fqtr</th>\n",
       "      <th>fyr</th>\n",
       "      <th>indfmt</th>\n",
       "      <th>consol</th>\n",
       "      <th>popsrc</th>\n",
       "      <th>datafmt</th>\n",
       "      <th>tic</th>\n",
       "      <th>...</th>\n",
       "      <th>gsector</th>\n",
       "      <th>gsubind</th>\n",
       "      <th>sic</th>\n",
       "      <th>spcindcd</th>\n",
       "      <th>spcseccd</th>\n",
       "      <th>spcsrc</th>\n",
       "      <th>tradedate</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>date</th>\n",
       "      <th>y_return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1045</td>\n",
       "      <td>19960331</td>\n",
       "      <td>1996</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AAL</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20302010.0</td>\n",
       "      <td>4512.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>C</td>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>44.7500</td>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>0.016621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1045</td>\n",
       "      <td>19960630</td>\n",
       "      <td>1996</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AAL</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20302010.0</td>\n",
       "      <td>4512.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>C</td>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>45.5000</td>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>-0.133531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1045</td>\n",
       "      <td>19960930</td>\n",
       "      <td>1996</td>\n",
       "      <td>3.0</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AAL</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20302010.0</td>\n",
       "      <td>4512.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>C</td>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>39.8125</td>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>0.101428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1045</td>\n",
       "      <td>19961231</td>\n",
       "      <td>1996</td>\n",
       "      <td>4.0</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AAL</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20302010.0</td>\n",
       "      <td>4512.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>C</td>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>44.0625</td>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>-0.065958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1045</td>\n",
       "      <td>19970331</td>\n",
       "      <td>1997</td>\n",
       "      <td>1.0</td>\n",
       "      <td>12</td>\n",
       "      <td>INDL</td>\n",
       "      <td>C</td>\n",
       "      <td>D</td>\n",
       "      <td>STD</td>\n",
       "      <td>AAL</td>\n",
       "      <td>...</td>\n",
       "      <td>20.0</td>\n",
       "      <td>20302010.0</td>\n",
       "      <td>4512.0</td>\n",
       "      <td>605.0</td>\n",
       "      <td>600.0</td>\n",
       "      <td>C</td>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>41.2500</td>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>0.114410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 656 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   gvkey  datadate  fyearq  fqtr  fyr indfmt consol popsrc datafmt  tic  ...  \\\n",
       "0   1045  19960331    1996   1.0   12   INDL      C      D     STD  AAL  ...   \n",
       "1   1045  19960630    1996   2.0   12   INDL      C      D     STD  AAL  ...   \n",
       "2   1045  19960930    1996   3.0   12   INDL      C      D     STD  AAL  ...   \n",
       "3   1045  19961231    1996   4.0   12   INDL      C      D     STD  AAL  ...   \n",
       "4   1045  19970331    1997   1.0   12   INDL      C      D     STD  AAL  ...   \n",
       "\n",
       "  gsector     gsubind     sic spcindcd  spcseccd  spcsrc  tradedate  \\\n",
       "0    20.0  20302010.0  4512.0    605.0     600.0       C 1996-06-01   \n",
       "1    20.0  20302010.0  4512.0    605.0     600.0       C 1996-09-01   \n",
       "2    20.0  20302010.0  4512.0    605.0     600.0       C 1996-12-01   \n",
       "3    20.0  20302010.0  4512.0    605.0     600.0       C 1997-03-01   \n",
       "4    20.0  20302010.0  4512.0    605.0     600.0       C 1997-06-01   \n",
       "\n",
       "  adj_close_q       date  y_return  \n",
       "0     44.7500 1996-06-01  0.016621  \n",
       "1     45.5000 1996-09-01 -0.133531  \n",
       "2     39.8125 1996-12-01  0.101428  \n",
       "3     44.0625 1997-03-01 -0.065958  \n",
       "4     41.2500 1997-06-01  0.114410  \n",
       "\n",
       "[5 rows x 656 columns]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.3 Calculate Financial Ratios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "fund_df['pe'] = fund_df.prccq / fund_df.epspxq\n",
    "fund_df['ps'] = fund_df.prccq / (fund_df.revtq/fund_df.cshoq)\n",
    "fund_df['pb'] = fund_df.prccq / ((fund_df.atq-fund_df.ltq)/fund_df.cshoq)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "items = [\n",
    "    'date', # Date\n",
    "    'gvkey',#gvkey unique identifier\n",
    "    'tic', # Ticker\n",
    "    'gsector',#gics sector 11\n",
    "    'oiadpq', # Quarterly operating income\n",
    "    'revtq', # Quartely revenue\n",
    "    'niq', # Quartely net income\n",
    "    'atq', # Total asset\n",
    "    'teqq', # Shareholder's equity\n",
    "    'epspiy', # EPS(Basic) incl. Extraordinary items\n",
    "    'ceqq', # Common Equity\n",
    "    'cshoq', # Common Shares Outstanding\n",
    "    'dvpspq', # Dividends per share\n",
    "    'actq', # Current assets\n",
    "    'lctq', # Current liabilities\n",
    "    'cheq', # Cash & Equivalent\n",
    "    'rectq', # Recievalbles\n",
    "    'cogsq', # Cost of  Goods Sold\n",
    "    'invtq', # Inventories\n",
    "    'apq',# Account payable\n",
    "    'dlttq', # Long term debt\n",
    "    'dlcq', # Debt in current liabilites\n",
    "    'ltq', # Liabilities   \n",
    "    'pe', #Price–earnings ratio\n",
    "    'ps', #Price–sales ratio\n",
    "    'pb', #Price-to-Book (P/B) Ratio\n",
    "    'adj_close_q',#adjusted close price\n",
    "    'y_return' #next quarter return\n",
    "]\n",
    "\n",
    "# Omit items that will not be used\n",
    "fund_data = fund_df[items]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>oiadpq</th>\n",
       "      <th>revtq</th>\n",
       "      <th>niq</th>\n",
       "      <th>atq</th>\n",
       "      <th>teqq</th>\n",
       "      <th>epspiy</th>\n",
       "      <th>...</th>\n",
       "      <th>invtq</th>\n",
       "      <th>apq</th>\n",
       "      <th>dlttq</th>\n",
       "      <th>dlcq</th>\n",
       "      <th>ltq</th>\n",
       "      <th>pe</th>\n",
       "      <th>ps</th>\n",
       "      <th>pb</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>y_return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>401.0</td>\n",
       "      <td>4308.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>19481.0</td>\n",
       "      <td>3896.0</td>\n",
       "      <td>2.02</td>\n",
       "      <td>...</td>\n",
       "      <td>606.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>6720.0</td>\n",
       "      <td>294.0</td>\n",
       "      <td>15585.0</td>\n",
       "      <td>44.306931</td>\n",
       "      <td>1.599698</td>\n",
       "      <td>1.768866</td>\n",
       "      <td>44.7500</td>\n",
       "      <td>0.016621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>586.0</td>\n",
       "      <td>4550.0</td>\n",
       "      <td>293.0</td>\n",
       "      <td>19662.0</td>\n",
       "      <td>5022.0</td>\n",
       "      <td>5.44</td>\n",
       "      <td>...</td>\n",
       "      <td>605.0</td>\n",
       "      <td>856.0</td>\n",
       "      <td>5469.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>14640.0</td>\n",
       "      <td>27.164179</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>1.648945</td>\n",
       "      <td>45.5000</td>\n",
       "      <td>-0.133531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>588.0</td>\n",
       "      <td>4562.0</td>\n",
       "      <td>282.0</td>\n",
       "      <td>20149.0</td>\n",
       "      <td>5293.0</td>\n",
       "      <td>8.53</td>\n",
       "      <td>...</td>\n",
       "      <td>623.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>5432.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>14856.0</td>\n",
       "      <td>26.021242</td>\n",
       "      <td>1.588311</td>\n",
       "      <td>1.368954</td>\n",
       "      <td>39.8125</td>\n",
       "      <td>0.101428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>4333.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>20497.0</td>\n",
       "      <td>5668.0</td>\n",
       "      <td>11.63</td>\n",
       "      <td>...</td>\n",
       "      <td>633.0</td>\n",
       "      <td>1068.0</td>\n",
       "      <td>4542.0</td>\n",
       "      <td>554.0</td>\n",
       "      <td>14829.0</td>\n",
       "      <td>21.759259</td>\n",
       "      <td>1.850767</td>\n",
       "      <td>1.414851</td>\n",
       "      <td>44.0625</td>\n",
       "      <td>-0.065958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>349.0</td>\n",
       "      <td>4426.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>20323.0</td>\n",
       "      <td>5824.0</td>\n",
       "      <td>1.67</td>\n",
       "      <td>...</td>\n",
       "      <td>611.0</td>\n",
       "      <td>990.0</td>\n",
       "      <td>4411.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>14499.0</td>\n",
       "      <td>49.401198</td>\n",
       "      <td>1.696227</td>\n",
       "      <td>1.289062</td>\n",
       "      <td>41.2500</td>\n",
       "      <td>0.114410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  gvkey  tic  gsector  oiadpq   revtq    niq      atq    teqq  \\\n",
       "0 1996-06-01   1045  AAL     20.0   401.0  4308.0  157.0  19481.0  3896.0   \n",
       "1 1996-09-01   1045  AAL     20.0   586.0  4550.0  293.0  19662.0  5022.0   \n",
       "2 1996-12-01   1045  AAL     20.0   588.0  4562.0  282.0  20149.0  5293.0   \n",
       "3 1997-03-01   1045  AAL     20.0   290.0  4333.0  284.0  20497.0  5668.0   \n",
       "4 1997-06-01   1045  AAL     20.0   349.0  4426.0  152.0  20323.0  5824.0   \n",
       "\n",
       "   epspiy  ...  invtq     apq   dlttq   dlcq      ltq         pe        ps  \\\n",
       "0    2.02  ...  606.0   880.0  6720.0  294.0  15585.0  44.306931  1.599698   \n",
       "1    5.44  ...  605.0   856.0  5469.0  273.0  14640.0  27.164179  1.820000   \n",
       "2    8.53  ...  623.0   912.0  5432.0  264.0  14856.0  26.021242  1.588311   \n",
       "3   11.63  ...  633.0  1068.0  4542.0  554.0  14829.0  21.759259  1.850767   \n",
       "4    1.67  ...  611.0   990.0  4411.0  430.0  14499.0  49.401198  1.696227   \n",
       "\n",
       "         pb  adj_close_q  y_return  \n",
       "0  1.768866      44.7500  0.016621  \n",
       "1  1.648945      45.5000 -0.133531  \n",
       "2  1.368954      39.8125  0.101428  \n",
       "3  1.414851      44.0625 -0.065958  \n",
       "4  1.289062      41.2500  0.114410  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77585, 28)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rename column names for the sake of readability\n",
    "fund_data = fund_data.rename(columns={\n",
    "    'oiadpq':'op_inc_q', # Quarterly operating income\n",
    "    'revtq':'rev_q', # Quartely revenue\n",
    "    'niq':'net_inc_q', # Quartely net income\n",
    "    'atq':'tot_assets', # Assets\n",
    "    'teqq':'sh_equity', # Shareholder's equity\n",
    "    'epspiy':'eps_incl_ex', # EPS(Basic) incl. Extraordinary items\n",
    "    'ceqq':'com_eq', # Common Equity\n",
    "    'cshoq':'sh_outstanding', # Common Shares Outstanding\n",
    "    'dvpspq':'div_per_sh', # Dividends per share\n",
    "    'actq':'cur_assets', # Current assets\n",
    "    'lctq':'cur_liabilities', # Current liabilities\n",
    "    'cheq':'cash_eq', # Cash & Equivalent\n",
    "    'rectq':'receivables', # Receivalbles\n",
    "    'cogsq':'cogs_q', # Cost of  Goods Sold\n",
    "    'invtq':'inventories', # Inventories\n",
    "    'apq': 'payables',# Account payable\n",
    "    'dlttq':'long_debt', # Long term debt\n",
    "    'dlcq':'short_debt', # Debt in current liabilites\n",
    "    'ltq':'tot_liabilities', # Liabilities   \n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>op_inc_q</th>\n",
       "      <th>rev_q</th>\n",
       "      <th>net_inc_q</th>\n",
       "      <th>tot_assets</th>\n",
       "      <th>sh_equity</th>\n",
       "      <th>eps_incl_ex</th>\n",
       "      <th>...</th>\n",
       "      <th>inventories</th>\n",
       "      <th>payables</th>\n",
       "      <th>long_debt</th>\n",
       "      <th>short_debt</th>\n",
       "      <th>tot_liabilities</th>\n",
       "      <th>pe</th>\n",
       "      <th>ps</th>\n",
       "      <th>pb</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>y_return</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>401.0</td>\n",
       "      <td>4308.0</td>\n",
       "      <td>157.0</td>\n",
       "      <td>19481.0</td>\n",
       "      <td>3896.0</td>\n",
       "      <td>2.02</td>\n",
       "      <td>...</td>\n",
       "      <td>606.0</td>\n",
       "      <td>880.0</td>\n",
       "      <td>6720.0</td>\n",
       "      <td>294.0</td>\n",
       "      <td>15585.0</td>\n",
       "      <td>44.306931</td>\n",
       "      <td>1.599698</td>\n",
       "      <td>1.768866</td>\n",
       "      <td>44.7500</td>\n",
       "      <td>0.016621</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>586.0</td>\n",
       "      <td>4550.0</td>\n",
       "      <td>293.0</td>\n",
       "      <td>19662.0</td>\n",
       "      <td>5022.0</td>\n",
       "      <td>5.44</td>\n",
       "      <td>...</td>\n",
       "      <td>605.0</td>\n",
       "      <td>856.0</td>\n",
       "      <td>5469.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>14640.0</td>\n",
       "      <td>27.164179</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>1.648945</td>\n",
       "      <td>45.5000</td>\n",
       "      <td>-0.133531</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>588.0</td>\n",
       "      <td>4562.0</td>\n",
       "      <td>282.0</td>\n",
       "      <td>20149.0</td>\n",
       "      <td>5293.0</td>\n",
       "      <td>8.53</td>\n",
       "      <td>...</td>\n",
       "      <td>623.0</td>\n",
       "      <td>912.0</td>\n",
       "      <td>5432.0</td>\n",
       "      <td>264.0</td>\n",
       "      <td>14856.0</td>\n",
       "      <td>26.021242</td>\n",
       "      <td>1.588311</td>\n",
       "      <td>1.368954</td>\n",
       "      <td>39.8125</td>\n",
       "      <td>0.101428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>290.0</td>\n",
       "      <td>4333.0</td>\n",
       "      <td>284.0</td>\n",
       "      <td>20497.0</td>\n",
       "      <td>5668.0</td>\n",
       "      <td>11.63</td>\n",
       "      <td>...</td>\n",
       "      <td>633.0</td>\n",
       "      <td>1068.0</td>\n",
       "      <td>4542.0</td>\n",
       "      <td>554.0</td>\n",
       "      <td>14829.0</td>\n",
       "      <td>21.759259</td>\n",
       "      <td>1.850767</td>\n",
       "      <td>1.414851</td>\n",
       "      <td>44.0625</td>\n",
       "      <td>-0.065958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>349.0</td>\n",
       "      <td>4426.0</td>\n",
       "      <td>152.0</td>\n",
       "      <td>20323.0</td>\n",
       "      <td>5824.0</td>\n",
       "      <td>1.67</td>\n",
       "      <td>...</td>\n",
       "      <td>611.0</td>\n",
       "      <td>990.0</td>\n",
       "      <td>4411.0</td>\n",
       "      <td>430.0</td>\n",
       "      <td>14499.0</td>\n",
       "      <td>49.401198</td>\n",
       "      <td>1.696227</td>\n",
       "      <td>1.289062</td>\n",
       "      <td>41.2500</td>\n",
       "      <td>0.114410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 28 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  gvkey  tic  gsector  op_inc_q   rev_q  net_inc_q  tot_assets  \\\n",
       "0 1996-06-01   1045  AAL     20.0     401.0  4308.0      157.0     19481.0   \n",
       "1 1996-09-01   1045  AAL     20.0     586.0  4550.0      293.0     19662.0   \n",
       "2 1996-12-01   1045  AAL     20.0     588.0  4562.0      282.0     20149.0   \n",
       "3 1997-03-01   1045  AAL     20.0     290.0  4333.0      284.0     20497.0   \n",
       "4 1997-06-01   1045  AAL     20.0     349.0  4426.0      152.0     20323.0   \n",
       "\n",
       "   sh_equity  eps_incl_ex  ...  inventories  payables  long_debt  short_debt  \\\n",
       "0     3896.0         2.02  ...        606.0     880.0     6720.0       294.0   \n",
       "1     5022.0         5.44  ...        605.0     856.0     5469.0       273.0   \n",
       "2     5293.0         8.53  ...        623.0     912.0     5432.0       264.0   \n",
       "3     5668.0        11.63  ...        633.0    1068.0     4542.0       554.0   \n",
       "4     5824.0         1.67  ...        611.0     990.0     4411.0       430.0   \n",
       "\n",
       "   tot_liabilities         pe        ps        pb  adj_close_q  y_return  \n",
       "0          15585.0  44.306931  1.599698  1.768866      44.7500  0.016621  \n",
       "1          14640.0  27.164179  1.820000  1.648945      45.5000 -0.133531  \n",
       "2          14856.0  26.021242  1.588311  1.368954      39.8125  0.101428  \n",
       "3          14829.0  21.759259  1.850767  1.414851      44.0625 -0.065958  \n",
       "4          14499.0  49.401198  1.696227  1.289062      41.2500  0.114410  \n",
       "\n",
       "[5 rows x 28 columns]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fund_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set data type to series\n",
    "date = fund_data['date'].to_frame('date').reset_index(drop=True)\n",
    "tic = fund_data['tic'].to_frame('tic').reset_index(drop=True)\n",
    "gvkey = fund_data['gvkey'].to_frame('gvkey').reset_index(drop=True)\n",
    "adj_close_q = fund_data['adj_close_q'].to_frame('adj_close_q').reset_index(drop=True)\n",
    "y_return = fund_data['y_return'].to_frame('y_return').reset_index(drop=True)\n",
    "gsector = fund_data['gsector'].to_frame('gsector').reset_index(drop=True)\n",
    "pe = fund_data['pe'].to_frame('pe').reset_index(drop=True)\n",
    "ps = fund_data['ps'].to_frame('ps').reset_index(drop=True)\n",
    "pb = fund_data['pb'].to_frame('pb').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Calculate financial ratios\n",
    "\n",
    "# Profitability ratios\n",
    "# Operating Margin\n",
    "OPM = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='OPM')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        OPM[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        OPM.iloc[i] = np.nan\n",
    "    else:\n",
    "        OPM.iloc[i] = np.sum(fund_data['op_inc_q'].iloc[i-3:i])/np.sum(fund_data['rev_q'].iloc[i-3:i])\n",
    "OPM=pd.Series(OPM).to_frame().reset_index(drop=True)\n",
    "\n",
    "# Net Profit Margin        \n",
    "NPM = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='NPM')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        NPM[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        NPM.iloc[i] = np.nan\n",
    "    else:\n",
    "        NPM.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/np.sum(fund_data['rev_q'].iloc[i-3:i])\n",
    "NPM=pd.Series(NPM).to_frame().reset_index(drop=True)\n",
    "\n",
    "# Return On Assets\n",
    "ROA = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='ROA')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        ROA[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        ROA.iloc[i] = np.nan\n",
    "    else:\n",
    "        ROA.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/fund_data['tot_assets'].iloc[i]\n",
    "ROA=pd.Series(ROA).to_frame().reset_index(drop=True)\n",
    "\n",
    "# Return on Equity\n",
    "ROE = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='ROE')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        ROE[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        ROE.iloc[i] = np.nan\n",
    "    else:\n",
    "        ROE.iloc[i] = np.sum(fund_data['net_inc_q'].iloc[i-3:i])/fund_data['sh_equity'].iloc[i]        \n",
    "ROE=pd.Series(ROE).to_frame().reset_index(drop=True)\n",
    "\n",
    "# For calculating valuation ratios in the next subpart, calculate per share items in advance\n",
    "# Earnings Per Share       \n",
    "EPS = fund_data['eps_incl_ex'].to_frame('EPS').reset_index(drop=True)\n",
    "\n",
    "# Book Per Share\n",
    "BPS = (fund_data['com_eq']/fund_data['sh_outstanding']).to_frame('BPS').reset_index(drop=True) # Need to check units\n",
    "\n",
    "#Dividend Per Share\n",
    "DPS = fund_data['div_per_sh'].to_frame('DPS').reset_index(drop=True)\n",
    "\n",
    "# Liquidity ratios\n",
    "# Current ratio\n",
    "cur_ratio = (fund_data['cur_assets']/fund_data['cur_liabilities']).to_frame('cur_ratio').reset_index(drop=True)\n",
    "\n",
    "# Quick ratio\n",
    "quick_ratio = ((fund_data['cash_eq'] + fund_data['receivables'] )/fund_data['cur_liabilities']).to_frame('quick_ratio').reset_index(drop=True)\n",
    "\n",
    "# Cash ratio\n",
    "cash_ratio = (fund_data['cash_eq']/fund_data['cur_liabilities']).to_frame('cash_ratio').reset_index(drop=True)\n",
    "\n",
    "\n",
    "# Efficiency ratios\n",
    "# Inventory turnover ratio\n",
    "inv_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='inv_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        inv_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        inv_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        inv_turnover.iloc[i] = np.sum(fund_data['cogs_q'].iloc[i-3:i])/fund_data['inventories'].iloc[i]\n",
    "inv_turnover=pd.Series(inv_turnover).to_frame().reset_index(drop=True)\n",
    "\n",
    "# Receivables turnover ratio       \n",
    "acc_rec_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='acc_rec_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        acc_rec_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        acc_rec_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        acc_rec_turnover.iloc[i] = np.sum(fund_data['rev_q'].iloc[i-3:i])/fund_data['receivables'].iloc[i]\n",
    "acc_rec_turnover=pd.Series(acc_rec_turnover).to_frame().reset_index(drop=True)\n",
    "\n",
    "# Payable turnover ratio\n",
    "acc_pay_turnover = pd.Series(np.empty(fund_data.shape[0],dtype=object),name='acc_pay_turnover')\n",
    "for i in range(0, fund_data.shape[0]):\n",
    "    if i-3 < 0:\n",
    "        acc_pay_turnover[i] = np.nan\n",
    "    elif fund_data.iloc[i,1] != fund_data.iloc[i-3,1]:\n",
    "        acc_pay_turnover.iloc[i] = np.nan\n",
    "    else:\n",
    "        acc_pay_turnover.iloc[i] = np.sum(fund_data['cogs_q'].iloc[i-3:i])/fund_data['payables'].iloc[i]\n",
    "acc_pay_turnover=pd.Series(acc_pay_turnover).to_frame().reset_index(drop=True)\n",
    "\n",
    "## Leverage financial ratios\n",
    "# Debt ratio\n",
    "debt_ratio = (fund_data['tot_liabilities']/fund_data['tot_assets']).to_frame('debt_ratio').reset_index(drop=True)\n",
    "\n",
    "# Debt to Equity ratio\n",
    "debt_to_equity = (fund_data['tot_liabilities']/fund_data['sh_equity']).to_frame('debt_to_equity').reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create a dataframe that merges all the ratios\n",
    "ratios = pd.concat([date,gvkey,tic,gsector,adj_close_q,y_return,OPM,NPM,ROA,ROE,EPS,BPS,DPS,\n",
    "                    cur_ratio,quick_ratio,cash_ratio,inv_turnover,acc_rec_turnover,acc_pay_turnover,\n",
    "                   debt_ratio,debt_to_equity,pe,ps,pb], axis=1).reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77585, 24)"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ratios.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Replace NAs infinite values with zero\n",
    "final_ratios = ratios.copy()\n",
    "final_ratios = final_ratios.fillna(0)\n",
    "final_ratios = final_ratios.replace(np.inf,0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_ratios.to_csv('final_ratios_raw.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(77585, 24)"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>y_return</th>\n",
       "      <th>OPM</th>\n",
       "      <th>NPM</th>\n",
       "      <th>ROA</th>\n",
       "      <th>ROE</th>\n",
       "      <th>...</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>inv_turnover</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>acc_pay_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>pe</th>\n",
       "      <th>ps</th>\n",
       "      <th>pb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>44.7500</td>\n",
       "      <td>0.016621</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.475086</td>\n",
       "      <td>0.179124</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.800010</td>\n",
       "      <td>4.000257</td>\n",
       "      <td>44.306931</td>\n",
       "      <td>1.599698</td>\n",
       "      <td>1.768866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>45.5000</td>\n",
       "      <td>-0.133531</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.497600</td>\n",
       "      <td>0.185348</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.744583</td>\n",
       "      <td>2.915173</td>\n",
       "      <td>27.164179</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>1.648945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>39.8125</td>\n",
       "      <td>0.101428</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.557623</td>\n",
       "      <td>0.269708</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.737307</td>\n",
       "      <td>2.806726</td>\n",
       "      <td>26.021242</td>\n",
       "      <td>1.588311</td>\n",
       "      <td>1.368954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>44.0625</td>\n",
       "      <td>-0.065958</td>\n",
       "      <td>0.117362</td>\n",
       "      <td>0.054545</td>\n",
       "      <td>0.035713</td>\n",
       "      <td>0.129146</td>\n",
       "      <td>...</td>\n",
       "      <td>0.573662</td>\n",
       "      <td>0.325368</td>\n",
       "      <td>17.292259</td>\n",
       "      <td>9.710564</td>\n",
       "      <td>10.249064</td>\n",
       "      <td>0.723472</td>\n",
       "      <td>2.616267</td>\n",
       "      <td>21.759259</td>\n",
       "      <td>1.850767</td>\n",
       "      <td>1.414851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>41.2500</td>\n",
       "      <td>0.114410</td>\n",
       "      <td>0.108888</td>\n",
       "      <td>0.063890</td>\n",
       "      <td>0.042267</td>\n",
       "      <td>0.147493</td>\n",
       "      <td>...</td>\n",
       "      <td>0.611247</td>\n",
       "      <td>0.329496</td>\n",
       "      <td>18.129296</td>\n",
       "      <td>9.005358</td>\n",
       "      <td>11.188889</td>\n",
       "      <td>0.713428</td>\n",
       "      <td>2.489526</td>\n",
       "      <td>49.401198</td>\n",
       "      <td>1.696227</td>\n",
       "      <td>1.289062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  gvkey  tic  gsector  adj_close_q  y_return       OPM       NPM  \\\n",
       "0 1996-06-01   1045  AAL     20.0      44.7500  0.016621  0.000000  0.000000   \n",
       "1 1996-09-01   1045  AAL     20.0      45.5000 -0.133531  0.000000  0.000000   \n",
       "2 1996-12-01   1045  AAL     20.0      39.8125  0.101428  0.000000  0.000000   \n",
       "3 1997-03-01   1045  AAL     20.0      44.0625 -0.065958  0.117362  0.054545   \n",
       "4 1997-06-01   1045  AAL     20.0      41.2500  0.114410  0.108888  0.063890   \n",
       "\n",
       "        ROA       ROE  ...  quick_ratio  cash_ratio  inv_turnover  \\\n",
       "0  0.000000  0.000000  ...     0.475086    0.179124      0.000000   \n",
       "1  0.000000  0.000000  ...     0.497600    0.185348      0.000000   \n",
       "2  0.000000  0.000000  ...     0.557623    0.269708      0.000000   \n",
       "3  0.035713  0.129146  ...     0.573662    0.325368     17.292259   \n",
       "4  0.042267  0.147493  ...     0.611247    0.329496     18.129296   \n",
       "\n",
       "   acc_rec_turnover  acc_pay_turnover  debt_ratio  debt_to_equity         pe  \\\n",
       "0          0.000000          0.000000    0.800010        4.000257  44.306931   \n",
       "1          0.000000          0.000000    0.744583        2.915173  27.164179   \n",
       "2          0.000000          0.000000    0.737307        2.806726  26.021242   \n",
       "3          9.710564         10.249064    0.723472        2.616267  21.759259   \n",
       "4          9.005358         11.188889    0.713428        2.489526  49.401198   \n",
       "\n",
       "         ps        pb  \n",
       "0  1.599698  1.768866  \n",
       "1  1.820000  1.648945  \n",
       "2  1.588311  1.368954  \n",
       "3  1.850767  1.414851  \n",
       "4  1.696227  1.289062  \n",
       "\n",
       "[5 rows x 24 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [],
   "source": [
    "features_column_financial=[ 'OPM', 'NPM', 'ROA', 'ROE', 'EPS', 'BPS', 'DPS', 'cur_ratio',\n",
    "       'quick_ratio', 'cash_ratio', 'inv_turnover', 'acc_rec_turnover',\n",
    "       'acc_pay_turnover', 'debt_ratio', 'debt_to_equity', 'pe', 'ps', 'pb']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [],
   "source": [
    "def handle_nan(df,features_column_financial):\n",
    "    ##handle nan, inf\n",
    "    df=df.drop(list(df[df.adj_close_q==0].index)).reset_index(drop=True)\n",
    "    df['y_return'] = pd.to_numeric(df['y_return'], errors='coerce')\n",
    "    for col in features_column_financial:\n",
    "        df[col] = pd.to_numeric(df[col], errors='coerce')\n",
    "    df['y_return'].replace([np.nan,np.inf,-np.inf], np.nan, inplace=True)\n",
    "    df[features_column_financial].replace([np.nan, np.inf, -np.inf], np.nan, inplace=True)\n",
    "    dropped_col = []\n",
    "    for col in features_column_financial:\n",
    "        if np.any(~np.isfinite(df[col])):\n",
    "            df.drop(columns=[col], axis=1, inplace=True)\n",
    "    df.dropna(axis=0, inplace=True)\n",
    "    df=df.reset_index(drop=True)\n",
    "    print(\"dropped_col: \",dropped_col)\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "dropped_col:  []\n"
     ]
    }
   ],
   "source": [
    "final_ratios=handle_nan(final_ratios,features_column_financial)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(74017, 18)"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>y_return</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>pe</th>\n",
       "      <th>ps</th>\n",
       "      <th>pb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [date, gvkey, tic, gsector, adj_close_q, y_return, EPS, BPS, DPS, cur_ratio, quick_ratio, cash_ratio, acc_rec_turnover, debt_ratio, debt_to_equity, pe, ps, pb]\n",
       "Index: []"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#final_ratios[final_ratios.adj_close_q==0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_ratios.date=final_ratios.date.apply(lambda x: x.strftime('%Y-%m-%d'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(74017, 18)"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>gvkey</th>\n",
       "      <th>tic</th>\n",
       "      <th>gsector</th>\n",
       "      <th>adj_close_q</th>\n",
       "      <th>y_return</th>\n",
       "      <th>EPS</th>\n",
       "      <th>BPS</th>\n",
       "      <th>DPS</th>\n",
       "      <th>cur_ratio</th>\n",
       "      <th>quick_ratio</th>\n",
       "      <th>cash_ratio</th>\n",
       "      <th>acc_rec_turnover</th>\n",
       "      <th>debt_ratio</th>\n",
       "      <th>debt_to_equity</th>\n",
       "      <th>pe</th>\n",
       "      <th>ps</th>\n",
       "      <th>pb</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1996-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>44.7500</td>\n",
       "      <td>0.016621</td>\n",
       "      <td>2.02</td>\n",
       "      <td>49.584416</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.717784</td>\n",
       "      <td>0.475086</td>\n",
       "      <td>0.179124</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.800010</td>\n",
       "      <td>4.000257</td>\n",
       "      <td>44.306931</td>\n",
       "      <td>1.599698</td>\n",
       "      <td>1.768866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-09-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>45.5000</td>\n",
       "      <td>-0.133531</td>\n",
       "      <td>5.44</td>\n",
       "      <td>55.186813</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.738051</td>\n",
       "      <td>0.497600</td>\n",
       "      <td>0.185348</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.744583</td>\n",
       "      <td>2.915173</td>\n",
       "      <td>27.164179</td>\n",
       "      <td>1.820000</td>\n",
       "      <td>1.648945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1996-12-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>39.8125</td>\n",
       "      <td>0.101428</td>\n",
       "      <td>8.53</td>\n",
       "      <td>58.164835</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.789316</td>\n",
       "      <td>0.557623</td>\n",
       "      <td>0.269708</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.737307</td>\n",
       "      <td>2.806726</td>\n",
       "      <td>26.021242</td>\n",
       "      <td>1.588311</td>\n",
       "      <td>1.368954</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1997-03-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>44.0625</td>\n",
       "      <td>-0.065958</td>\n",
       "      <td>11.63</td>\n",
       "      <td>62.285714</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.803090</td>\n",
       "      <td>0.573662</td>\n",
       "      <td>0.325368</td>\n",
       "      <td>9.710564</td>\n",
       "      <td>0.723472</td>\n",
       "      <td>2.616267</td>\n",
       "      <td>21.759259</td>\n",
       "      <td>1.850767</td>\n",
       "      <td>1.414851</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1997-06-01</td>\n",
       "      <td>1045</td>\n",
       "      <td>AAL</td>\n",
       "      <td>20.0</td>\n",
       "      <td>41.2500</td>\n",
       "      <td>0.114410</td>\n",
       "      <td>1.67</td>\n",
       "      <td>64.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.851293</td>\n",
       "      <td>0.611247</td>\n",
       "      <td>0.329496</td>\n",
       "      <td>9.005358</td>\n",
       "      <td>0.713428</td>\n",
       "      <td>2.489526</td>\n",
       "      <td>49.401198</td>\n",
       "      <td>1.696227</td>\n",
       "      <td>1.289062</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  gvkey  tic  gsector  adj_close_q  y_return    EPS        BPS  \\\n",
       "0  1996-06-01   1045  AAL     20.0      44.7500  0.016621   2.02  49.584416   \n",
       "1  1996-09-01   1045  AAL     20.0      45.5000 -0.133531   5.44  55.186813   \n",
       "2  1996-12-01   1045  AAL     20.0      39.8125  0.101428   8.53  58.164835   \n",
       "3  1997-03-01   1045  AAL     20.0      44.0625 -0.065958  11.63  62.285714   \n",
       "4  1997-06-01   1045  AAL     20.0      41.2500  0.114410   1.67  64.000000   \n",
       "\n",
       "   DPS  cur_ratio  quick_ratio  cash_ratio  acc_rec_turnover  debt_ratio  \\\n",
       "0  0.0   0.717784     0.475086    0.179124          0.000000    0.800010   \n",
       "1  0.0   0.738051     0.497600    0.185348          0.000000    0.744583   \n",
       "2  0.0   0.789316     0.557623    0.269708          0.000000    0.737307   \n",
       "3  0.0   0.803090     0.573662    0.325368          9.710564    0.723472   \n",
       "4  0.0   0.851293     0.611247    0.329496          9.005358    0.713428   \n",
       "\n",
       "   debt_to_equity         pe        ps        pb  \n",
       "0        4.000257  44.306931  1.599698  1.768866  \n",
       "1        2.915173  27.164179  1.820000  1.648945  \n",
       "2        2.806726  26.021242  1.588311  1.368954  \n",
       "3        2.616267  21.759259  1.850767  1.414851  \n",
       "4        2.489526  49.401198  1.696227  1.289062  "
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_ratios.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "#final_ratios=final_ratios[final_ratios.date<'2022-12-01'].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(73917, 18)"
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#final_ratios.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_ratios.to_csv('final_ratios.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.4 Separate by sector"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "for sec, df_ in list(final_ratios.groupby('gsector')):\n",
    "    df_.to_excel(f\"sector{int(sec)}.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  },
  "vscode": {
   "interpreter": {
    "hash": "ad2bdc8ecc057115af97d19610ffacc2b4e99fae6737bb82f5d7fb13d2f2c186"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
